-- Create tables
create table Orders(
	OrdersID int identity primary key,
	EmployeeID int not null,
	IssueDate date not null,
	DueDate date not null,
	Returned bit default 0,
	[Status] bit default 1,
	constraint FK_Orders_Employee foreign key (EmployeeID) references Employee(EmployeeID)
)
go
create table OrdersDetails(
	OrdersID int not null,
	BookID int not null,
	ReturnDate date,
	Reason nvarchar(100),
	[Money] float default 0,
	[Status] bit default 1,
	constraint FK_OrdersDetails_Orders foreign key (OrdersID) references Orders(OrdersID),
	constraint FK_OrdersDetails_Book foreign key (BookID) references Book(BookID)
)
go

--Create store procedure insert new record to table Book
create proc sp_InsertBook
	@BookID int output,
	@Title nvarchar(100),
	@AuthorName nvarchar(30),
	@CategoryID	int,
	@PublisherID int,
	@CallNumber nvarchar(15),
	@ISBN nvarchar(15),
	@Price float,
	@Page int,
	@IssueStatus bit, 
	@Status bit
	
as
	insert into Book values
	(@Title,@AuthorName,@CategoryID,@PublisherID,@CallNumber,@ISBN,@Price,@Page,@IssueStatus,@Status)
set
	@BookID = @@identity;

--Create store procedure insert new record to table Category	
create proc sp_InsertCategory
	@CategoryID int output,
	@CategoryName nvarchar(50),
	@Status bit
as		
	insert into Category values
	(@CategoryName,@Status)
set 
	@CategoryID = @@identity;

	
--Create store procedure insert new record to table Publisher
create proc sp_InsertPublisher
	@PublisherID int output,
	@PublisherName nvarchar(100),
	@Status bit
as
	insert into Publisher values
	(@PublisherName,@Status)	
set 
	@PublisherID =	@@identity;	
	
	
--Create store procedure insert new record to table User
create proc sp_InsertUser
	@UserName nvarchar(30),
	@Password nvarchar(30),
	@Authorization bit,
	@Status bit
as
	insert into [User] values
	(@UserName,@Password,@Authorization,@Status)
	
--Create store procedure insert new record to table Orders
create proc sp_InsertOrders
	@OrdersID int output,
	@EmployeeID int,
	@IssueDate date,
	@DueDate date,
	@Returned bit,
	@Status bit
as
	insert into [Orders] values
	(@EmployeeID,@IssueDate,@DueDate,@Returned,@Status)
set
	@OrdersID =	@@identity;	
go
	
--Create store procedure insert new record to table OrdersDetails
create proc sp_InsertOrdersDetails
	@OrdersID int,
	@BookID int,
	@Status bit
as
	insert into OrdersDetails(OrdersID,BookID,[Status]) values
	(@OrdersID,@BookID,@Status)
go

--Create store procedure insert new record to table Employee
create proc sp_InsertEmployee
	@EmployeeID int output,
	@EmployeeName nvarchar(30),
	@Address nvarchar(100),
	@PhoneNumber nvarchar(15),
	@DepartmentID int,
	@Status bit,
	@Gender bit
as
	insert into Employee values
	(@EmployeeName,@Address,@PhoneNumber,@DepartmentID,@Status,@Gender)
set
	@EmployeeID =	@@identity;	
		
--Create store procedure insert new record to table Department
create proc sp_InsertDepartment
	@DepartmentID int output,
	@DepartmentName nvarchar(50),		
	@Status bit
as
	insert into Department values
	(@DepartmentName,@Status)
set
	@DepartmentID =	@@identity;		

----------------------------------------------------------------
----------------------------------------------------------------

--Create store procedure update table Book
create proc sp_UpdateBook
	@BookID int ,
	@Title nvarchar(100),
	@AuthorName nvarchar(30),
	@CategoryID	int,
	@PublisherID int,
	@CallNumber nvarchar(15),
	@ISBN nvarchar(15),
	@Price float,
	@Page int,
	@IssueStatus bit, 
	@Status bit
as
	update Book 
set 
	Title=@Title,AuthorName=@AuthorName,
	CategoryID=@CategoryID,PublisherID=@PublisherID,
	CallNumber=@CallNumber,ISBN=@ISBN,Price=@Price,
	Page=@Page,IssueStatus=@IssueStatus,[Status]=@Status
where
	BookID=@BookID
	
	
--Create store procedure update table Category
create proc sp_UpdateCategory
	@CategoryID int output,
	@CategoryName nvarchar(50),
	@Status bit
as		
	update Category
set
	CategoryName=@CategoryName,	[Status]=@Status
where 
	CategoryID=@CategoryID

--Create store procedure update table Publisher
create proc sp_UpdatePublisher
	@PublisherID int output,
	@PublisherName nvarchar(100),
	@Status bit
as		
	update Publisher
set
	PublisherName=@PublisherName,[Status]=@Status
where 
	PublisherID=@PublisherID


--Create store procedure update table User
create proc sp_UpdateUser
	@UserName nvarchar(30),
	@Password nvarchar(30),
	@Authorization bit,
	@Status bit
as
	update 	[User]
set 
	UserName=@UserName,	[Password]=@Password,[Authorization]=@Authorization,[Status]=@Status

	
--Create store procedure update table Orders
create proc sp_UpdateOrders
	@OrdersID int,
	@EmployeeID int,
	@IssueDate date,
	@DueDate date,
	@Returned bit,
	@Status bit
as
	update [Orders]
set
	EmployeeID=@EmployeeID,	IssueDate=@IssueDate,DueDate=@DueDate,Returned=@Returned, [Status] = @Status
where
	OrdersID=@OrdersID
go

--Create store procedure update table OrdersDetails
create proc sp_UpdateOrdersDetails
	@OrdersID int,
	@BookID int,
	@ReturnDate date,
	@Reason nvarchar(100),
	@Money float,
	@Status bit
as
	update 	OrdersDetails
set
	ReturnDate=@ReturnDate,Reason=@Reason,[Money]=@Money, [Status]=@Status
where
	OrdersID=@OrdersID and BookID=@BookID
go

--Create store procedure update table Employee
create proc sp_UpdateEmployee
	@EmployeeID int ,
	@EmployeeName nvarchar(30),
	@Address nvarchar(100),
	@PhoneNumber nvarchar(15),
	@DepartmentID int,
	@Status bit,
	@Gender bit
as
	update Employee
	
set
	EmployeeName=@EmployeeName,[Address]=@Address,PhoneNumber=@PhoneNumber,DepartmentID=@DepartmentID,[Status]=@Status,Gender=@Gender
where
	EmployeeID=@EmployeeID
go	

--Create store procedure update table Department
create proc sp_UpdateDepartment
	@DepartmentID int ,
	@DepartmentName nvarchar(50),		
	@Status bit
as
	update Department
set
	@DepartmentName=@DepartmentName,[Status]=@Status
where
	DepartmentID=@DepartmentID	
go	
---------------------------------------------------
---------------------------------------------------

--Create store procedure get table Book with status = true

create proc sp_GetBook
	@Status bit
as 
	select*from Book where [Status] = @Status
go			
--Create store procedure get table Category status = true

create proc sp_GetCategory
	@Status bit
as 
	select*from Category where [Status] = @Status
	
--Create store procedure get table Department status = true
go
create proc sp_GetDepartment
	@Status bit
as 
	select*from Department where [Status] = @Status
	
--Create store procedure get table Employee status = true
go
create proc sp_GetEmployee
	@Status bit
as 
	select*from  Employee where [Status] = @Status
go	
--Create store procedure get table Orders status = true

alter proc sp_GetOrders
	@Status bit
as 
	select * from  [Orders] where [Status] = @Status
go
--Create store procedure get table OrdersDetails status = true

create proc sp_GetOrdersDetails
	@Status bit
as 
	select * from  OrdersDetails where [Status] = @Status
go
--Create store procedure get table Publisher status = true

create proc sp_GetPublisher
	@Status bit	
as 
	select*from  Publisher where [Status] = @Status

--Create store procedure get table User status = true
go
create proc sp_GetUser
	@Status bit
as 
	select*from [User] where [Status] = @Status
go
-- create store procedure get table Orders and OrdersDetails by EmployeeID
create proc sp_GetOrdersAndOrdersDetailsByEmployeeID
	@EmployeeID int
as
	select * from [Orders] where EmployeeID=@EmployeeID
go

create proc sp_GetOrdersAndOrdersDetailsByEmployeeID
	@OrdersID int
as
	select * from [Orders] where EmployeeID=@EmployeeID
go
select * from Category
select * from Book
select * from [Orders]
select * from OrdersDetails
select * from Category
select * from Employee
select * from Publisher

delete [Orders]
delete OrdersDetails
update Book set IssueStatus=1 where BookID > 1

sp_GetOrders 1


insert into Book(Title,AuthorName,CategoryID,PublisherID,CallNumber,ISBN,Price,Page,IssueStatus) 
values
go
update Employee set EmployeeName='HELEN' where EmployeeID=2